﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PBOffice.Models;

namespace PBOffice.DAL
{
    public class MsgToUserService
    {
        public List<MessageToUser> GetAllMsgToUserByMsgId(int id)
        {
            List<MessageToUser> list = new List<MessageToUser>();
            string sql = string.Format("select * from MessageToUser where MessageId={0}", id);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    MessageToUser user = new MessageToUser();


                    user.Id = (int)dr["Id"];
                    user.MessageId = (int)dr["MessageId"];
                    user.ToUserId = dr["ToUserId"].ToString();
                    user.IfRead = (int)dr["IfRead"];
                    list.Add(user);
                }
            }
            return list;
        }

        public List<MessageToUser> GetAllMsgToUserByUserId(string userId)
        {
            List<MessageToUser> list = new List<MessageToUser>();
            string sql = string.Format("select * from MessageToUser where ToUserId='{0}'", userId);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    MessageToUser user = new MessageToUser();


                    user.Id = (int)dr["Id"];
                    user.MessageId = (int)dr["MessageId"];
                    user.ToUserId = dr["ToUserId"].ToString();
                    user.IfRead = (int)dr["IfRead"];
                    list.Add(user);
                }
            }
            return list;
        }
        public void DeleteMessageToUserByMessageId(int id)
        {
            string sql = string.Format("delete [MessageToUser] where MessageId={0}", id);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void AddMessageToUser(MessageToUser touser)
        {
            string sql = "insert [MessageToUser] (MessageId,ToUserId,IfRead) values(@MessageToUser,@ToUserId,@IfRead)";
            SqlParameter[] spa = {
                                     new SqlParameter("@MessageId",touser.MessageId),
                                     new SqlParameter("@ToUserId",touser.ToUserId),
                                     new SqlParameter("@IfRead",touser.IfRead),
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public int GetCountMsgToUserByToUserId(string name)
        {
            string sql = string.Format("select count(*) from MessageToUser where ToUserId='{0}'", name);
            return Convert.ToInt32(SqlHelp.ExecuteScalar(SqlHelp.connectionstring, CommandType.Text, sql));
        }
        public int GetCountMsgToUserByToUserIdAndIfRead(string name)
        {
            string sql = string.Format("select count(*) from MessageToUser where IfRead=0 and ToUserId='{0}'", name);
            return Convert.ToInt32(SqlHelp.ExecuteScalar(SqlHelp.connectionstring, CommandType.Text, sql));
        }
        public void DeleteMsgToUserByToUserIdAndMsgId(int id,string name)
        {
            string sql = string.Format("delete [MessageToUser] where MessageId={0} and ToUserId='{1}'", id, name);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
    }
}
